package educrypt.database.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Observable;

import educrypt.database.Connector;

public class KeywordDAO extends Observable {

	/* Singleton */
	private KeywordDAO() {
	}

	public static KeywordDAO getInstance() {
		return SingletonHolder.instance;
	}

	private static class SingletonHolder {
		public static final KeywordDAO instance = new KeywordDAO();
	}

	/* END Singleton */

	public List<String> find(int cipher, String alphabetName) {
		final String sqlFindKeyword = "SELECT * from Keyword WHERE cipher="
				+ cipher + " AND alphabetName='" + alphabetName + "'";
		List<String> keywords = new ArrayList<String>();
		try {
			Connection conn = Connector.getConnection();
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(sqlFindKeyword);
			while (rs.next()) {
				keywords.add(rs.getString("symbols"));
			}
			rs.close();
			st.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return keywords;
	}

	public boolean exists(int cipher, String alphabetName, String keyword) {
		final String sqlExistsKeyword = "SELECT COUNT(*) from Keyword WHERE cipher=? AND alphabetName=? AND symbols=?";
		boolean exists = false;
		try {
			Connection conn = Connector.getConnection();
			PreparedStatement ps = conn.prepareStatement(sqlExistsKeyword);
			ps.setInt(1, cipher);
			ps.setString(2, alphabetName);
			ps.setString(3, keyword);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				exists = rs.getInt(1) > 0;
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return exists;
	}

	public void create(int cipher, String alphabetName, String keyword) {
		if (!exists(cipher, alphabetName, keyword)) {
			final String sqlCreateKeyword = "INSERT INTO Keyword (cipher, alphabetName, symbols) VALUES(?,?,?)";
			try {
				Connection conn = Connector.getConnection();
				PreparedStatement ps = conn.prepareStatement(sqlCreateKeyword,
						Statement.RETURN_GENERATED_KEYS);
				ps.setInt(1, cipher);
				ps.setString(2, alphabetName);
				ps.setString(3, keyword);
				ps.executeUpdate();
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			this.setChanged();
			this.notifyObservers(new Object[] { cipher, alphabetName });
		}
	}

	public void delete(int cipher, String alphabetName, String keyword) {
		final String sqlDeleteKeyword = "DELETE FROM Keyword WHERE cipher=? AND alphabetName=? AND symbols=?";
		try {
			Connection conn = Connector.getConnection();
			PreparedStatement ps = conn.prepareStatement(sqlDeleteKeyword);
			ps.setInt(1, cipher);
			ps.setString(2, alphabetName);
			ps.setString(3, keyword);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		this.setChanged();
		this.notifyObservers(new Object[] { cipher, alphabetName });
	}
}
